package com.ums.scumspay.bigdataimport.QryFile;

import com.alibaba.excel.ExcelReader;
import com.ums.scumspay.bigdataimport.constant.FilePathConstant;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK;

public class ExcelUtilEasyexcel {

    public static void main(String[] args) {
        try {
            // 对读取Excel表格标题测试
            InputStream is = new FileInputStream("C:\\Users\\13610\\Desktop\\2023.4绩效数据表\\表12.宝信返佣奖励-202304.xlsx");
            OutputStream os = new FileOutputStream("C:\\Users\\13610\\Documents\\2023.4.rtf");
            ExcelUtilEasyexcel excelReader = new ExcelUtilEasyexcel();

            Map<String,ArrayList<ArrayList<String>>> map = excelReader.readExcelHead(is,0);
            ArrayList<MultipartFile> list1 = new ArrayList<>();
            exportExcel(list1,"202307");
            //循环取出map
            for (Map.Entry<String, ArrayList<ArrayList<String>>> entry : map.entrySet()) {
                String key = entry.getKey();
                ArrayList<ArrayList<String>> value = entry.getValue();
                for (ArrayList<String> list : value) {
                    System.out.println(list + "\r\n");
                    os.write((list+ "\r\n").getBytes());
                }
            }
            os.close();
            is.close();

        }catch (FileNotFoundException e) {

            System.out.println("未找到指定路径的文件!");

            e.printStackTrace();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }
    public static void exportExcel(List<MultipartFile> fileDirectory, String month) throws IOException {
        File fileWrite = new File(FilePathConstant.filePath + "\\" + month);
        if (!fileWrite.exists()) {
            fileWrite.mkdir();
            fileWrite.createNewFile();
        }else {
            deleteDir(fileWrite);
            fileWrite.mkdir();
            fileWrite.createNewFile();
        }
        //将string类型的month转换时间类型,方便月份计算
        YearMonth yearMonth = YearMonth.parse(month, DateTimeFormatter.ofPattern("yyyyMM"));
        String lastYearMonth = yearMonth.minusMonths(1).format(DateTimeFormatter.ofPattern("yyyyMM"));

        FileOutputStream outputStream = new FileOutputStream(fileWrite + "\\kpiData.sql",true);
        outputStream.write(("INSERT INTO KPI_SUM_ALL(DEPT_CODE,DEPT_NAME,STAFF_CODE,STAFF_NAME,MONTH,KPI_DEPT_NAME)" +
                "SELECT DEPT_CODE,DEPT_NAME,STAFF_CODE,STAFF_NAME,'"
                + yearMonth + "',KPI_DEPT_NAME FROM KPI_SUM_ALL WHERE MONTH='" + lastYearMonth + "';"+"\r\n").getBytes());

        for (MultipartFile file : fileDirectory) {
            // 获取原始文件名
            String originalFilename = file.getOriginalFilename();
            InputStream inputStream = file.getInputStream();

            //正式sql文件
            try {

                if (originalFilename.contains("新拓展优质商户")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable1(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("线上收单")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable2(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("存量计提")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable3(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("客户经理当月")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable4(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("存量月均")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable5(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("产品计价奖励")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable7(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("营销联盟活动奖励")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 0);
                    KpiDataService.readTable8(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("行业ERP")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable9(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("天天富")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable10(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("宝信返佣")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable12(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("行缴奖励")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable14(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("商户让利")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable20(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("其他")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable21(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("表22")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable22(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("低效终端清理撤机")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 3);
                    KpiDataService.readTable23(map.get("sheet0"), outputStream, month);
                } else if (originalFilename.contains("商服")) {
                    Map<String, ArrayList<ArrayList<String>>> map = ExcelUtilEasyexcel.readExcelHead(inputStream, 1);
                    KpiDataService.readTable24(map.get("sheet1"), outputStream, month);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            inputStream.close();
        }
        firstExcuteSql(outputStream,month);
        outputStream.write(("UPDATE KPI_SUM_ALL SET CML_TER_DEPRECIATION = 5.95,CML_ADD_DEPRECIATION = 5.95," +
                "CML_OLD_TER_DEPRECIATION=5.61,CML_OLD_DEPRECATION_AMT=5.61 WHERE MONTH = '" + month + "'"+"\r\n").getBytes());
        outputStream.close();
    }


    //读取xlsx表格表头的内容
    public static Map<String,ArrayList<ArrayList<String>>> readExcelHead(InputStream is,int ingnoreRows) {

        HashMap map = new HashMap<String,ArrayList<ArrayList<String>>>();

        try {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            Workbook wb = new XSSFWorkbook(is);
            FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);

            int numberOfSheets = wb.getNumberOfSheets();
            for (int sheetNums = 0; sheetNums < numberOfSheets; sheetNums++){
                ArrayList<ArrayList<String>> lists = new ArrayList<ArrayList<String>>();
                Sheet sheet = wb.getSheetAt(sheetNums);
                Row row = sheet.getRow(sheetNums);
                if (row == null) {
                    continue;
                }
                int firstDataRowIndex = findFirstNonEmptyRowIndex(sheet);
                int lastCellNum = getFirstRowColumnCount(sheet);
                int firstCellNum = sheet.getRow(firstDataRowIndex).getFirstCellNum();

                int lastRowNum = sheet.getLastRowNum();
                if(firstDataRowIndex == 0){
                    firstDataRowIndex = firstDataRowIndex + ingnoreRows;
                }

                for (int i = firstDataRowIndex; i <= lastRowNum; i++) {
                    ArrayList<String> list = new ArrayList<String>();
                    row = sheet.getRow(i);
                    if(row == null){
                        continue;
                    }
                    //读取每一行的单元格
                    for (int j = firstCellNum; j < firstCellNum+lastCellNum; j++) {
                        if (row.getCell(j) != null) {
                            switch (row.getCell(j).getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    list.add(row.getCell(j).getStringCellValue());
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    list.add(String.valueOf(row.getCell(j).getNumericCellValue()));
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    list.add(String.valueOf(row.getCell(j).getBooleanCellValue()));
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
                                    if (row.getCell(j).getCellFormula().contains("VLOOKUP")){
                                        list.add(String.valueOf(row.getCell(j)));
                                    }else {
                                        CellValue evaluate = formulaEvaluator.evaluate(row.getCell(j));
                                        list.add(String.valueOf(evaluate.getNumberValue()));
                                    }
                                    break;
                                case CELL_TYPE_BLANK:
                                    list.add("0");
                                    break;
                                case Cell.CELL_TYPE_ERROR:
                                    list.add("0");
                                    break;
                                default:
                                    list.add("0");
                            }
                        }else {
                            list.add("0");
                        }

                    }
                    lists.add(list);
                }
                map.put("sheet"+ sheetNums, lists);
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return map;
    }

    // 获取第一行的有效列数
    private static int getFirstRowColumnCount(Sheet sheet) {
        Row firstRow = sheet.getRow(findFirstNonEmptyRowIndex(sheet)); // 获取第一行

        int columnCount = 0;
        if (firstRow != null) {
        // 遍历一行的单元格
                for (int cellIndex = firstRow.getFirstCellNum(); cellIndex < firstRow.getPhysicalNumberOfCells(); cellIndex++) {
                    Cell cell = firstRow.getCell(cellIndex);
                    if (cell!=null && isMergedCell(sheet,cell)) {
                        columnCount++;
                    }else if (cell != null && cell.getCellType() != CELL_TYPE_BLANK){
                        columnCount++;
                    }
                }
                return columnCount;
            }

        return 0; // 如果第一行为空，则返回0
    }
    private static int findFirstNonEmptyRowIndex(Sheet sheet) {
        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                for (int cellIndex = row.getFirstCellNum(); cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    if (cell != null && cell.getCellType() != CELL_TYPE_BLANK) {
                        return rowIndex; // 找到第一个非空单元格所在的行
                    }
                }
            }
        }
        return -1; // 如果没有找到包含非空单元格的行，返回-1或其他适当的值
    }

    private static boolean isMergedCell(Sheet sheet, Cell cell) {
        for (CellRangeAddress region : sheet.getMergedRegions()) {
            if (region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                return true;
            }
        }
        return false;
    }

    public static void firstExcuteSql(FileOutputStream fileOutputStream,String month) throws IOException {
        for (int i = 0; i < 16; i++) {
            //数字型数组
            String[] kpiName = {"KPI_TABLE1", "KPI_TABLE2", "KPI_TABLE3", "KPI_TABLE4",
                    "KPI_TABLE5", "KPI_TABLE7", "KPI_TABLE8", "KPI_TABLE9", "KPI_TABLE10",
                    "KPI_TABLE12", "KPI_TABLE14",  "KPI_TABLE20", "KPI_TABLE21", "KPI_TABLE22", "KPI_TABLE23", "KPI_TABLE24"};
            fileOutputStream.write(("merge into " + kpiName[i] + " P  using SYS_USER t1  on (P.STAFF_NAME = t1.USER_NAME  AND P.MONTH='" + month + "')\n" +
                    "when matched then    \n" +
                    "update set P.DEPT_CODE = t1.DEPT_NO,STAFF_CODE=t1.USER_GH; \n" +
                    "commit;\n" +
                    "--更新部门名称\n" +
                    "merge into " + kpiName[i] + " P  using DEPT_INFO t1  on (P.DEPT_CODE = t1.DEPT_NO AND P.MONTH='" + month + "' )    \n" +
                    "when matched then    \n" +
                    "update set P.DEPT_NAME = t1.DEPT_NAME; \n" +
                    "commit;\n").getBytes());

        }
    }

    private static boolean deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();
            for (int i=0; i<children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    return false;
                }
            }
        }
        // 目录此时为空，可以删除
        return dir.delete();
    }
}